package com.example.dao;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.example.domain.User;

@Repository
public interface IndexDao extends JpaRepository<User, Integer> {

    @Query(nativeQuery = true, value = "SELECT u.*,p.password,r.roleName from ((users u INNER JOIN passwd p ON u.user_Id = p.user_Id)\r\n" +
            "INNER JOIN user_role ur ON ur.user_Id = u.user_Id)\r\n" +
            "INNER JOIN role r ON ur.role_Id = r.role_Id \r\n" +
            "WHERE u.account = :userName  AND p.password = :password")
    List<Map<Object, String>> login(@Param("userName") String userName, @Param("password") String password);

    @Query(nativeQuery = true, value = "SELECT u.*,p.password,r.roleName from ((users u INNER JOIN passwd p ON u.user_Id = p.user_Id)\r\n" +
            "INNER JOIN user_role ur ON ur.user_Id = u.user_Id)\r\n" +
            "INNER JOIN role r ON ur.role_Id = r.role_Id \r\n" +
            "WHERE u.user_Id = :userId")
    List<Map<Object, String>> userInfoById(@Param("userId") Integer userId);

    @Query(nativeQuery = true, value = "SELECT clt.typeName as classTypeName,cs.className,cut.typeName as courseTypeName,c.courseName FROM (((((course c INNER JOIN class_course cc ON c.course_Id = cc.course_Id)\r\n" +
            "INNER JOIN classes cs ON cs.class_Id = cc.class_Id)\r\n" +
            "INNER JOIN class_type clt ON clt.class_type_Id = cs.class_type_Id )\r\n" +
            "INNER JOIN course_type cut ON cut.course_type_Id = c.course_type_Id )\r\n" +
            "INNER JOIN student_class sc ON sc.class_Id = cs.class_Id)\r\n" +
            "INNER JOIN users u ON sc.user_Id = u.user_Id\r\n" +
            "WHERE u.user_Id = :userId")
    List<Map<Object, String>> courseInfoById(@Param("userId") Integer userId);

    @Query(nativeQuery = true, value = "SELECT COUNT(*) as userNumber FROM users")
    List<Map<Object, String>> calculateAllUsers();

    @Query(nativeQuery = true, value = "SELECT COUNT(case when r.roleName='管理员' then 1 end) as admin,\r\n" +
            "COUNT(case when r.roleName='学生' then 1 end) as student,\r\n" +
            "COUNT(case when r.roleName='老师' then 1 end) as teacher \r\n" +
            "FROM (users u INNER JOIN user_role ur ON u.user_Id = ur.user_Id)\r\n" +
            "INNER JOIN role r ON r.role_Id = ur.role_Id")
    List<Map<Object, String>> calculateAllUsersByRoleName();

    @Query(nativeQuery = true, value = "SELECT COUNT(*) as vecateNumber FROM ask_for_leave where status = '未批阅'")
    List<Map<Object, String>> calculateVecateNumber();

    @Query(nativeQuery = true, value = "SELECT COUNT(*) as transferClassNumber FROM transfer_class where transferStatus = '未批阅'")
    List<Map<Object, String>> calculateTransferNumber();

    @Query(nativeQuery = true, value = "SELECT COUNT(*) as quitClassNumber FROM quit_class where quitStatus = '未批阅'")
    List<Map<Object, String>> calculateQuitNumber();

    @Query(nativeQuery = true, value = "SELECT cut.typeName as courseTypeName,c.courseName \r\n" +
            "FROM ((course c INNER JOIN course_teacher ct ON c.course_Id = ct.course_Id)\r\n" +
            "INNER JOIN course_type cut ON cut.course_type_Id = c.course_type_Id )\r\n" +
            "INNER JOIN users u ON ct.user_Id = u.user_Id\r\n" +
            "WHERE u.user_Id = :userId")
    List<Map<Object, String>> courseInfoByTeacherId(@Param("userId") Integer userId);

    @Query(nativeQuery = true, value = "SELECT r.roleName,m.* FROM (role r INNER JOIN role_menu rm ON r.role_Id = rm.role_Id)\r\n" +
            "INNER JOIN menus m ON m.menu_Id = rm.menu_Id\r\n" +
            "where r.roleName = :roleName ")
    List<Map<Object, String>> roleMenuResult(@Param("roleName") String roleName);

}
